﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using QuanLyKhachSan.BusinessObject;
using QuanLyKhachSan.DataAccess;
namespace QuanLyKhachSan.BusinessLogic
{
    class NhanVienBL
    {
        ConnectData connData = new ConnectData();
        //lấy danh sách nhân viên
        public DataTable LayDanhSachNhanVien() {
            string sql = "LayDanhSachNhanVien";
            return connData.GetDataTable(sql);
        }
        //lấy danh sach giới tính
        public DataTable LayDSGioiTinh() {
            string sql = "select distinct GioiTinh,"
                + "NameGT = case GioiTinh when 0 then N'Nam'"
                + "when 1 then N'Nữ' end from NhanVien";
            return connData.GetDataTable(sql);
        }

        public DataTable TimKiemNhanVien(string timKiem)
        {
            string sql = "SELECT STT ,MaNhanVien, TenNhanVien, NgaySinh, GioiTinh, DiaChi, SoDienThoai, MaChucVu, MaNhom FROM NhanVien where TenNhanVien like '%" + timKiem + "%'";
            return connData.GetDataTable(sql);
        }
       
        //thêm mới nhân viên
        public void ThemNV(NhanVienBO nv) {

                
                //string sql = string.Format("INSERT INTO NhanVien (MaNhanVien, TenNhanVien, UserName, PassWord, NgaySinh, GioiTinh, DiaChi, SoDienThoai, MaChucVu, MaNhom, Anh) "
                //     + "VALUES ('{0}','{1}','{2}','{3}','{4}',{5},'{6}','{7}','{8}','{9}','{10}')", nv.MaNhanVien, nv.TenNhanVien, nv.UserName, nv.PassWord, nv.NgaySinh.ToString("MM-dd-yyyy"), nv.GioiTinh, nv.DiaChi, nv.SoDienThoai, nv.MaChucVu, nv.MaNhom, nv.Anh);
                
                
                //if (connData.ExeCuteQuery(sql))
                //{
                //    MessageBox.Show("them moi nhan vien thanh cong", "thong bao", MessageBoxButtons.OK, MessageBoxIcon.Information);
                //}
            SqlConnection conn = connData.Connect1();
            conn.Open();
            string sql = "ThemMoiNhanVien";
            SqlCommand objCmd = new SqlCommand(sql, conn);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add("@MaNhanVien", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@TenNhanVien", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@PassWord", SqlDbType.NVarChar, 50);
            objCmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime);
            objCmd.Parameters.Add("@GioiTinh", SqlDbType.Int);
            objCmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaChucVu", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaNhom", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@Anh", SqlDbType.Image);


            objCmd.Parameters["@MaNhanVien"].Value = nv.MaNhanVien;
            objCmd.Parameters["@TenNhanVien"].Value = nv.TenNhanVien;
            objCmd.Parameters["@UserName"].Value = nv.UserName;
            objCmd.Parameters["@PassWord"].Value = nv.PassWord;
            objCmd.Parameters["@NgaySinh"].Value = nv.NgaySinh;
            objCmd.Parameters["@GioiTinh"].Value = nv.GioiTinh;
            objCmd.Parameters["@DiaChi"].Value = nv.DiaChi;
            objCmd.Parameters["@SoDienThoai"].Value = nv.SoDienThoai;
            objCmd.Parameters["@MaChucVu"].Value = nv.MaChucVu;
            objCmd.Parameters["@MaNhom"].Value = nv.MaNhom;
            objCmd.Parameters["@Anh"].Value = nv.Anh;
            objCmd.ExecuteNonQuery();
            conn.Close();
        }

        //
        public string NextID() {
            return Utilities.NextID(connData.GetLastID("NhanVien","MaNhanVien"),"NV");
        }
        
        public bool CheckExists(string manv) {
            if (connData.CheckExistValue("NhanVien", "MaNhanVien", manv))
            {
                return true;
            } return false;
        }

        //sửa
        public void SuaNV(NhanVienBO nv)
        {
                //string sql = string.Format("UPDATE NhanVien SET TenNhanVien = '{0}', UserName ='{1}', PassWord='{2}', NgaySinh = '{3}', "
                //                + "GioiTinh = '{4}', DiaChi = '{5}', SoDienThoai = '{6}', MaChucVu = '{7}', MaNhom = '{8}' WHERE MaNhanVien = '{9}' ",
                //                         nv.TenNhanVien, nv.UserName, nv.PassWord, nv.NgaySinh.ToString("MM-dd-yyyy"), nv.GioiTinh, nv.DiaChi, nv.SoDienThoai, nv.MaChucVu, nv.MaNhom, nv.MaNhanVien);
                //if (connData.ExeCuteQuery(sql))
                //{
                //    MessageBox.Show("sua nhan vien thanh cong", "thong bao", MessageBoxButtons.OK, MessageBoxIcon.Information);
                //}
            SqlConnection conn = connData.Connect1();
            conn.Open();
            string sql = "SuaNhanVien";
            SqlCommand objCmd = new SqlCommand(sql, conn);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add("@MaNhanVien", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@TenNhanVien", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@PassWord", SqlDbType.NVarChar, 50);
            objCmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime);
            objCmd.Parameters.Add("@GioiTinh", SqlDbType.Int);
            objCmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaChucVu", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaNhom", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@Anh", SqlDbType.Image);


            objCmd.Parameters["@MaNhanVien"].Value = nv.MaNhanVien;
            objCmd.Parameters["@TenNhanVien"].Value = nv.TenNhanVien;
            objCmd.Parameters["@UserName"].Value = nv.UserName;
            objCmd.Parameters["@PassWord"].Value = nv.PassWord;
            objCmd.Parameters["@NgaySinh"].Value = nv.NgaySinh;
            objCmd.Parameters["@GioiTinh"].Value = nv.GioiTinh;
            objCmd.Parameters["@DiaChi"].Value = nv.DiaChi;
            objCmd.Parameters["@SoDienThoai"].Value = nv.SoDienThoai;
            objCmd.Parameters["@MaChucVu"].Value = nv.MaChucVu;
            objCmd.Parameters["@MaNhom"].Value = nv.MaNhom;
            objCmd.Parameters["@Anh"].Value = nv.Anh;
            objCmd.ExecuteNonQuery();
            conn.Close();
        }

        //xóa
        public void Delete(string nv) {
            string sql = "Delete from NhanVien where MaNhanVien='" + nv + "'";
            connData.ExeCuteQuery(sql);
        }


        public NhanVienBO LayThongTinUser(string username)
        {
            string sql = "select UserName, Password, TenNhanVien, a.MaNhom, TenNhom"
                + " from NhanVien a, NhomNguoiDung b where a.MaNhom = b.MaNhom"
                + " and UserName = '" + username + "'";
            DataTable tbUser = connData.GetDataTable(sql);
            NhanVienBO nvBO = new NhanVienBO();
            if (tbUser.Rows.Count > 0)
            {
                DataRow rowUser = connData.GetDataTable(sql).Rows[0];
                nvBO.UserName = rowUser["UserName"].ToString();
                nvBO.PassWord = rowUser["PassWord"].ToString();
                nvBO.TenNhanVien = rowUser["TenNhanVien"].ToString();
                nvBO.MaNhom = rowUser["MaNhom"].ToString();
                nvBO.TenNhom = rowUser["TenNhom"].ToString();
            }
            else
            {
                nvBO.UserName = "";
                nvBO.PassWord = "";
                nvBO.TenNhanVien = "";
                nvBO.MaNhom = "";
                nvBO.TenNhom = "";
            }
            return nvBO;
        }

        public SqlDataReader LayAnhSinhVien(NhanVienBO NV)
        {
            int Nparameter = 1;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@TimKiem";
            value[0] = NV.MaNhanVien;
            return connData.LayAnh("LayAnhSinhVien", name, value, Nparameter);
        }
    }
}
